<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@page import="connection.DBConnection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Profit And Loss Account</title>
</head>
<body>
<form name="profitandloss">
<table align="center" border=5 width=700>

<tr>
	<th colspan=4><font color=skyblue>Profit and Loss Account</font></th>
</tr>

<tr>
	<th><font color=skyblue>Expenses</font></th>
	<th><font color=skyblue>Rs.</font></th>
	<th><font color=skyblue>Income</font></th>
	<th><font color=skyblue>Rs.</font></th>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>


<%
 
		DBConnection dbc=new DBConnection();
		Connection con=dbc.getNewConnection();
		Statement st = null;
		ResultSet rs = null;   
		
		double openingstock=0.0;
		
		try
   		{
		   st=con.createStatement();
		   //rs=st.executeQuery("SELECT code, name, sum(amount), groupname FROM ( (SELECT raccid AS code, raccname AS name, -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT racc2id AS code, raccname2 AS name, -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyaccid AS code, pyaccname AS name, pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyacc2id AS code, pyaccname2 AS name, pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jaccid AS code, jaccdeb AS name, jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT jacc2id AS code, jacccred AS name, -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Capital'");
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Opening Stock'");
		   while(rs.next())
		   {
		        //String r11=rs.getString(1); // code	        
		        //String r12=rs.getString(2); // name
		        double r13=rs.getDouble(1); // amount
		        String r14=rs.getString(2); // group name
	
				openingstock=r13;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<td>To Opening Stock</td>
	<td><%=openingstock %></td>
	
<%
		double sales=0.0,salestransport=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("select SUM(samount),SUM(strans) from SalesVoucher2");
		   while(rs.next())
		   {
		        sales=rs.getDouble(1); // sales amount
		        salestransport=rs.getDouble(2); // sales transport charges
		   }
   		}
		catch(Exception e) {}
%>
	
<%
		double credit=0.0,credittransport=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("select SUM(cdnet),SUM(cdtrans) from CreditNote");
		   while(rs.next())
		   {
		        credit=rs.getDouble(1); // sales amount
		        credittransport=rs.getDouble(2); // sales transport charges
		   }
   		}
		catch(Exception e) {}
%>

	<td>By Sales</td>
	<td><%=sales-credit %></td>
</tr>

<%
		double purchase=0.0, purchasetransport=0.0, purchaseothercharges=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("select SUM(pvneta),SUM(pvtrans),SUM(pvot) from PurchaseVoucher2");
		   while(rs.next())
		   {
		        purchase=rs.getDouble(1); // purchase amount
		        purchasetransport=rs.getDouble(2); // purchase transport charges
		        purchaseothercharges=rs.getDouble(3); // purchase other charges
		   }
   		}
		catch(Exception e) {}
%>

<%
		double debit=0.0, debittransport=0.0, debitothercharges=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("select SUM(dbnet),SUM(dbtrans),SUM(dbot) from DebitNote");
		   while(rs.next())
		   {
		        debit=rs.getDouble(1); // purchase amount
		        debittransport=rs.getDouble(2); // purchase transport charges
		        debitothercharges=rs.getDouble(3); // purchase other charges
		   }
   		}
		catch(Exception e) {}
%>


<tr>
	<td>To Purchases</td>
	<td><%=purchase-debit %></td>
	
	<td>By Sales Transport Charges</td>
	<td><%=salestransport-credittransport %></td>
</tr>

<tr>
	<td>To Purchase Transport Charges</td>
	<td><%=purchasetransport-debittransport %></td>

<%
		double closingstock=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Closing Stock'");
		   while(rs.next())
		   {
		        double cs1=rs.getDouble(1); // amount
		        String cs2=rs.getString(2); // group name
	
				closingstock=cs1;	
		   }
   		}
		catch(Exception e) {}
%>
	
	<td>By Closing Stock</td>
	<td><%=closingstock %></td>
</tr>

<tr>
	<td>To Purchase Other Expenses</td>
	<td><%=purchaseothercharges-debitothercharges %></td>
	<td></td>
	<td></td>
</tr>

<% 		double grossprofit=0.0;
		double Total2=salestransport+closingstock+sales-credit-credittransport;
		
		double Subtotal1=openingstock+purchase+purchasetransport+purchaseothercharges-debit-debittransport-debitothercharges;
	
		grossprofit=Total2-Subtotal1;
	
		double Total1=grossprofit+Subtotal1;
		//grossprofit=sales+salestransport+closingstock-openingstock-purchase-purchasetransport-purchaseothercharges;
		
		
%>

<tr>
	<td>To Gross Profit</td>
	<td><%=grossprofit %></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<tr>
	<td>Total1</td>
	<td><%=Total1 %></td>
	<td>Total2</td>
	<td><%=Total2 %></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<%
		double administrativecharges=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Administrative Charges'");
		   while(rs.next())
		   {
		        double ad1=rs.getDouble(1); // amount
		        String ad2=rs.getString(2); // group name
	
				administrativecharges=ad1;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<td>To Administrative Charges</td>
	<td><%=administrativecharges %></td>
	<td>By Gross Profit</td>
	<td><%=grossprofit %></td>
</tr>

<%
		double bankcharges=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Bank Charges and Interest'");
		   while(rs.next())
		   {
		        double bi1=rs.getDouble(1); // amount
		        String bi2=rs.getString(2); // group name
	
				bankcharges=bi1;	
		   }
   		}
		catch(Exception e) {}
%>
<tr>
	<td>To Bank Charges and Interest</td>
	<td><%=bankcharges %></td>
	
<%
		double indirectincome=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT ramount AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT ramount2 AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(pyamount) AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT -(pyamount2) AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT jamount2 AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Indirect Income'");
		   while(rs.next())
		   {
		        double ii1=rs.getDouble(1); // amount
		        String ii2=rs.getString(2); // group name
	
				indirectincome=ii1;	
		   }
   		}
		catch(Exception e) {}
%>
	<td>By Indirect Income</td>
	<td><%=indirectincome %></td>
</tr>

<%
		double sellingandmarketing=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Selling and Marketing Expenses'");
		   while(rs.next())
		   {
		        double sm1=rs.getDouble(1); // amount
		        String sm2=rs.getString(2); // group name
	
				sellingandmarketing=sm1;	
		   }
   		}
		catch(Exception e) {}
%>
<tr>
	<td>To Selling and Marketing Expenses</td>
	<td><%=sellingandmarketing %></td>
	<td></td>
	<td></td>
</tr>

<%
		double salarywages=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Salary and Wages'");
		   while(rs.next())
		   {
		        double sw1=rs.getDouble(1); // amount
		        String sw2=rs.getString(2); // group name
	
				salarywages=sw1;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<td>To Salary Wages</td>
	<td><%=salarywages %></td>
	<td></td>
	<td></td>
</tr>

<%
		double depreciation=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Depreciation'");
		   while(rs.next())
		   {
		        double op1=rs.getDouble(1); // amount
		        String op2=rs.getString(2); // group name
	
				depreciation=op1;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<td>To Depreciation</td>
	<td><%=depreciation %></td>
	<td></td>
	<td></td>
</tr>

<%
		double otherexpenses=0.0;
		
		try
   		{
		   st=con.createStatement();
		   
		   rs=st.executeQuery("SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Other Expenses'");
		   while(rs.next())
		   {
		        double oe1=rs.getDouble(1); // amount
		        String oe2=rs.getString(2); // group name
	
				otherexpenses=oe1;	
		   }
   		}
		catch(Exception e) {}
%>

<tr>
	<td>To Other Expenses</td>
	<td><%=otherexpenses %></td>
	<td></td>
	<td></td>
</tr>

<%  	double netprofit=0.0;
		
		double Total3=grossprofit+indirectincome;
		double subtotal4=administrativecharges+bankcharges+sellingandmarketing+salarywages+depreciation+otherexpenses;
		
		netprofit= grossprofit+indirectincome-administrativecharges-bankcharges-sellingandmarketing-salarywages-depreciation-otherexpenses;
		
		double Total4=netprofit+subtotal4;
%>
<tr>
	<td>To Net Profit</td>
	<td><%=netprofit %></td>
	<td></td>
	<td></td>
</tr>

<tr><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td><td>&nbsp</td></tr>

<tr>
	<td>Total3</td>
	<td><%=Total4 %></td>
	<td>Total4</td>
	<td><%=Total3 %></td>
</tr>
</table>
</form>

</body>
</html>